Análisis Colaboradores


Fuente: Bases de Cartera


Mayo 2025

 
In [1]:
import pandas as pd
import numpy as np
from prophet import Prophet
from prophet.diagnostics import cross_validation
from prophet.diagnostics import performance_metrics
import plotly.graph_objs as go
import matplotlib.pyplot as plt
import plotly.io as pio
import plotly.express as px

pio.templates.default = "presentation"  # o cualquier otro nombre de tema
import re

import sqlalchemy as sa
from sqlalchemy.engine import URL
import urllib
import pyodbc

print('- Paquetes importados exitosamente')

C:\Users\binvelam\Anaconda3\lib\site-packages\scipy\__init__.py:155: UserWarning: A NumPy version >=1.18.5 and <1.25.0 is required for this version of SciPy (detected version 1.26.4
  warnings.warn(f"A NumPy version >={np_minversion} and <{np_maxversion}"

- Paquetes importados exitosamente

Credenciales

In [2]:
file  = open('config.txt')
lines = file.readlines()
lines = [s.strip() for s in lines]
In [3]:
try:
    cnxn = pyodbc.connect('DRIVER={ODBC Driver 11 for SQL Server};Server='
                          +lines[15]+
                          ';DATABASE='+lines[16]+
                          ';trusted_connection=yes;')
    print("conexión exitosa")    
except: 
    print('error al intentar conectar')

conexión exitosa

Bases de Cartera

In [4]:
DF1_BC = pd.read_sql("SELECT Identificacion,Nombre,DiasVencido, FechaProceso, Operacion, YearEmision, SaldoSb204, SaldoDificul, SaldoDificul30, SaldoDificul60, SegmentoFinal FROM BCPersonas WHERE SegmentoFinal IN ('Consumo', 'Vivienda')",cnxn)

print(DF1_BC.shape)
DF1_BC.head(2)

C:\Users\binvelam\Anaconda3\lib\site-packages\pandas\io\sql.py:762: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
  warnings.warn(

(202540, 11)
Out[4]:
Identificacion Nombre DiasVencido FechaProceso Operacion YearEmision SaldoSb204 SaldoDificul SaldoDificul30 SaldoDificul60 SegmentoFinal
0 0100875905 Merchan Luco Marina Dolores 0.0 2019-12-30 830300962 2018 9114.21 0.0 0.0 0.0 Consumo
1 1500494453 Jimenez Ramirez John Manrique 0.0 2020-04-30 660302394 2018 7377.28 0.0 0.0 0.0 Consumo

Colaboradores y Ex Colaboradores

In [5]:
DF2_ECBI = pd.read_sql("SELECT DISTINCT epIdentificacion as Identificacion FROM [172.16.66.208].[DATAMART].[dbo].[ExPersonalBanco]",cnxn)
DF2_ECBI['IdColaborador'] = 'Ex Colaborador'
DF2_ECBI[['Identificacion']] = DF2_ECBI[['Identificacion']].astype('str')
print(DF2_ECBI.shape)
DF2_ECBI.head(2)

C:\Users\binvelam\Anaconda3\lib\site-packages\pandas\io\sql.py:762: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
  warnings.warn(

(1705, 2)
Out[5]:
Identificacion IdColaborador
0 0921357141 Ex Colaborador
1 1713935847 Ex Colaborador
In [6]:
DF2_CBI = pd.read_sql("SELECT DISTINCT pbIdentificacion as Identificacion FROM [172.16.66.208].[DATAMART].[dbo].[MKPersonalBanco]",cnxn)
DF2_CBI['IdColaborador'] = 'Colaborador'
DF2_CBI[['Identificacion']] = DF2_CBI[['Identificacion']].astype('str')
print(DF2_CBI.shape)
DF2_CBI.head(2)

C:\Users\binvelam\Anaconda3\lib\site-packages\pandas\io\sql.py:762: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
  warnings.warn(

(3575, 2)
Out[6]:
Identificacion IdColaborador
0 0102117306 Colaborador
1 0102132008 Colaborador
In [7]:
DF4 = pd.concat([DF2_ECBI, DF2_CBI])
In [8]:
DF3 = pd.read_excel('Id_Tipo_Colaborador.xlsx', dtype={'Identificacion': str})
In [9]:
DF3 = pd.concat([DF3,DF4])
DF3 = DF3.drop_duplicates(subset='Identificacion', keep='first')

Cruce

In [10]:
DF = DF3.merge(DF1_BC, how = 'inner', on = ['Identificacion'])
DF['IdColaborador'] = np.where((DF.DiasVencido > 60) & (DF.IdColaborador == "Colaborador"),"Ex Colaborador" ,DF.IdColaborador)
In [11]:
DF
Out[11]:
Identificacion IdColaborador Nombre DiasVencido FechaProceso Operacion YearEmision SaldoSb204 SaldoDificul SaldoDificul30 SaldoDificul60 SegmentoFinal
0 1705894713 Ex Colaborador Carrera Torres Maria Soledad 0.0 2022-09-30 314934 2010 18168.19 0.0 0.0 0.0 Vivienda
1 1705894713 Ex Colaborador Carrera Torres Maria Soledad 0.0 2022-10-31 314934 2010 17711.27 0.0 0.0 0.0 Vivienda
2 1705894713 Ex Colaborador Carrera Torres Maria Soledad 0.0 2022-11-30 314934 2010 17255.35 0.0 0.0 0.0 Vivienda
3 1705894713 Ex Colaborador Carrera Torres Maria Soledad 0.0 2022-12-30 314934 2010 16791.59 0.0 0.0 0.0 Vivienda
4 1705894713 Ex Colaborador Carrera Torres Maria Soledad 0.0 2022-03-31 314934 2010 20820.07 0.0 0.0 0.0 Vivienda
... ... ... ... ... ... ... ... ... ... ... ... ...
10628 0914819826 Ex Colaborador Rodriguez Dominguez Eduardo Luis 0.0 2021-06-30 100307729 2007 10508.52 0.0 0.0 0.0 Vivienda
10629 0914819826 Ex Colaborador Rodriguez Dominguez Eduardo Luis 8.0 2021-07-30 100307729 2007 10508.52 0.0 0.0 0.0 Vivienda
10630 0914819826 Ex Colaborador Rodriguez Dominguez Eduardo Luis 9.0 2021-08-31 100307729 2007 9798.69 0.0 0.0 0.0 Vivienda
10631 0914819826 Ex Colaborador Rodriguez Dominguez Eduardo Luis 0.0 2021-09-30 100307729 2007 8869.69 0.0 0.0 0.0 Vivienda
10632 0914819826 Ex Colaborador Rodriguez Dominguez Eduardo Lu 7.0 2021-10-29 100307729 2007 8869.69 0.0 0.0 0.0 Vivienda

10633 rows × 12 columns

Consumo

In [12]:
DF1 = DF[DF.SegmentoFinal == 'Consumo']
DF1 = (DF1
         .groupby(['FechaProceso','IdColaborador'], as_index=False)
         .agg(Dificultades60 = ('SaldoDificul30', np.nansum), Dificultades30 = ('SaldoDificul30', np.nansum),SaldoTotal = ('SaldoSb204', np.nansum))
         .reset_index()
        )

DF1 = DF1.assign( Morosidad30 = DF1.Dificultades30/DF1.SaldoTotal, Morosidad60 = DF1.Dificultades60/DF1.SaldoTotal)
DF1['Morosidad60_pct'] = DF1['Morosidad60'] * 100
DF1['Morosidad30_pct'] = DF1['Morosidad30'] * 100
In [13]:
fig = go.Figure()

colores = {
    'Colaborador': '#1f77b4',
    'Ex Colaborador': '#d62728'
}

tipos = DF1['IdColaborador'].unique()

for tipo in tipos:
    df_tipo = DF1[DF1['IdColaborador'] == tipo]

    # === BARRA apilada (saldo) === con leyenda activa
    fig.add_trace(go.Bar(
        x=df_tipo['FechaProceso'],
        y=df_tipo['SaldoTotal'],
        name=tipo,  # Solo esta traza tiene showlegend=True
        marker_color=colores[tipo],
        opacity=0.5,
        yaxis='y2',
        legendgroup=tipo,
        showlegend=True
    ))

    # === LÍNEA de morosidad === sin leyenda (agrupada visualmente)
    fig.add_trace(go.Scatter(
        x=df_tipo['FechaProceso'],
        y=df_tipo['Morosidad30'] * 100,
        mode='lines+markers',
        name=tipo,  # Mismo nombre pero sin leyenda
        marker_color=colores[tipo],
        legendgroup=tipo,
        showlegend=False,
        hovertemplate='<b>Fecha:</b> %{x|%Y-%m-%d}<br><b>Morosidad 60:</b> %{y:.1f}%<extra></extra>'
    ))

fig.update_layout(
    title='Consumo - Morosidad 30',
    xaxis_title='Fecha de Proceso',
    yaxis=dict(title='Morosidad 30 (%)', tickformat='.1f'),
    yaxis2=dict(title='Saldo Total', overlaying='y', side='right', showgrid=False),
    barmode='stack',
    plot_bgcolor='white',
    paper_bgcolor='white',
    legend=dict(orientation='h', y=1.1, x=0.5, xanchor='center')
)

fig.show()

Vivienda

In [14]:
DF1 = DF[DF.SegmentoFinal == 'Vivienda']
DF1 = (DF1
         .groupby(['FechaProceso','IdColaborador'], as_index=False)
         .agg(Dificultades60 = ('SaldoDificul30', np.nansum), Dificultades30 = ('SaldoDificul30', np.nansum),SaldoTotal = ('SaldoSb204', np.nansum))
         .reset_index()
        )

DF1 = DF1.assign( Morosidad30 = DF1.Dificultades30/DF1.SaldoTotal, Morosidad60 = DF1.Dificultades60/DF1.SaldoTotal)
DF1['Morosidad60_pct'] = DF1['Morosidad60'] * 100
DF1['Morosidad30_pct'] = DF1['Morosidad30'] * 100
In [15]:
fig = go.Figure()

colores = {
    'Colaborador': '#1f77b4',
    'Ex Colaborador': '#d62728'
}

tipos = DF1['IdColaborador'].unique()

for tipo in tipos:
    df_tipo = DF1[DF1['IdColaborador'] == tipo]

    # === BARRA apilada (saldo) === con leyenda activa
    fig.add_trace(go.Bar(
        x=df_tipo['FechaProceso'],
        y=df_tipo['SaldoTotal'],
        name=tipo,  # Solo esta traza tiene showlegend=True
        marker_color=colores[tipo],
        opacity=0.5,
        yaxis='y2',
        legendgroup=tipo,
        showlegend=True
    ))

    # === LÍNEA de morosidad === sin leyenda (agrupada visualmente)
    fig.add_trace(go.Scatter(
        x=df_tipo['FechaProceso'],
        y=df_tipo['Morosidad60'] * 100,
        mode='lines+markers',
        name=tipo,  # Mismo nombre pero sin leyenda
        marker_color=colores[tipo],
        legendgroup=tipo,
        showlegend=False,
        hovertemplate='<b>Fecha:</b> %{x|%Y-%m-%d}<br><b>Morosidad 60:</b> %{y:.1f}%<extra></extra>'
    ))

fig.update_layout(
    title='Vivienda - Morosidad 60',
    xaxis_title='Fecha de Proceso',
    yaxis=dict(title='Morosidad 60 (%)', tickformat='.1f'),
    yaxis2=dict(title='Saldo Total', overlaying='y', side='right', showgrid=False),
    barmode='stack',
    plot_bgcolor='white',
    paper_bgcolor='white',
    legend=dict(orientation='h', y=1.1, x=0.5, xanchor='center')
)

fig.show()